﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoBackupDatabase.DBConection;
using System.Data;
using AutoBackupDatabase.ENT;
using System.Data.SqlClient;

namespace AutoBackupDatabase.DAL
{
    class TABLEDAL
    {
        private DbConnection conn;
        public TABLEDAL()
        {
            conn = new DbConnection();
        }

        public List<TABLEENT> GET_TABLE_ALL()
        {
            string query = string.Format("exec GET_TABLE_ALL ");
            DataTable ds = conn.executeGetQuery(query);
            List<TABLEENT> List = new List<TABLEENT>();
            foreach (DataRow item in ds.Rows)
            {
                TABLEENT ent = new TABLEENT();
                ent.AREAID = item["AREAID"].ToString();
                ent.TABLEID = int.Parse( item["TABLEID"].ToString());
                ent.TABLENAME = item["TABLENAME"].ToString();
                ent.AREANAME = item["AREANAME"].ToString();
                List.Add(ent);
            }

            return List;
        }
        public bool INSERTTABLE(TABLEENT ent)
        {
            string query = "exec  INSERTTABLE @TABLENAME,@AREAID";
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@TABLENAME", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(ent.TABLENAME);
            sqlParameters[1] = new SqlParameter("@AREAID", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(ent.AREAID);
            return conn.executeInsertQuery(query, sqlParameters);
        }
        public bool UPDATETABLEBYTABLEID(TABLEENT ent)
        {
            string query = "exec  UPDATETABLEBYTABLEID @TABLEID,@TABLENAME, @AREAID";
            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@TABLENAME", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(ent.TABLENAME);
            sqlParameters[1] = new SqlParameter("@AREAID", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(ent.AREAID);
            sqlParameters[2] = new SqlParameter("@TABLEID", SqlDbType.Int);
            sqlParameters[2].Value = Convert.ToString(ent.TABLEID);

            return conn.executeUpdateQuery(query, sqlParameters);
        }
        public bool DELTELEAREABYAREAID(TABLEENT ent)
        {
            string query = "exec  DELTELETABLEBYTABLEID @TABLEID";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@TABLEID", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToString(ent.TABLEID);
            return conn.executeDeleteQuery(query, sqlParameters);
        }

        public List<TABLEENT> GETTABLEHAVEGUESTBYAREAID(string AREAID)
        {
            string query = string.Format("exec GETTABLEHAVEGUESTBYAREAID @AREAID");

            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@AREAID", SqlDbType.VarChar);
            sqlParameters[0].Value = AREAID;

            DataTable ds = conn.executeSelectQuery(query, sqlParameters);

            TABLEENT ent;
            List<TABLEENT> List = new List<TABLEENT>(); foreach (DataRow item in ds.Rows)
            {
                ent = new TABLEENT();
                ent.TABLEID = int.Parse(item["TABLEID"].ToString());
                ent.TABLENAME = item["TABLENAME"].ToString();
                ent.AREAID = item["AREAID"].ToString();
                List.Add(ent);
            }

            return List;
        }

        public List<TABLEENT> GETTABLENOTHAVEGUESTBYAREAID(string AREAID)
        {
            string query = string.Format("exec GETTABLENOTHAVEGUESTBYAREAID @AREAID");

            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@AREAID", SqlDbType.VarChar);
            sqlParameters[0].Value = AREAID;

            DataTable ds = conn.executeSelectQuery(query, sqlParameters);

            TABLEENT ent;
            List<TABLEENT> List = new List<TABLEENT>(); 
            foreach (DataRow item in ds.Rows)
            {
                ent = new TABLEENT();
                ent.TABLEID = int.Parse(item["TABLEID"].ToString());
                ent.TABLENAME = item["TABLENAME"].ToString();
                ent.AREAID = item["AREAID"].ToString();
                List.Add(ent);
            }

            return List;
        }
    }
}
